package com.rchou.sts.dao.jdbc;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.annotation.Resource;
import javax.sql.DataSource;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.springframework.beans.factory.BeanCreationException;
import org.springframework.beans.factory.InitializingBean;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.ResultSetExtractor;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

import com.rchou.sts.dao.StudentDao;
import com.rchou.sts.domain.LvlHistory;
import com.rchou.sts.domain.Student;

@Repository("studentDao")
@Transactional
public class JdbcStudentDao implements StudentDao, InitializingBean 
{
	private Log log = LogFactory.getLog(JdbcStudentDao.class);
	
	private DataSource dataSource;
	private JdbcTemplate jdbcTemplate;
	
	@Resource(name="dataSource")
	public void setDataSource(DataSource dataSource) 
	{
		this.dataSource = dataSource;
		this.jdbcTemplate = new JdbcTemplate(dataSource);
	}
	
	@Override
	public void afterPropertiesSet() throws Exception 
	{
		if (dataSource == null) {
			throw new BeanCreationException("Must set dataSource on StudenttDao");
		}
	}
	
	public List<Student> findAll() 
	{
		/*List<Student> students = (List<Student>)jdbcTemplate.queryForObject(
				"SELECT * FROM student ORDER BY id DESC", List.class);
		return students;*/
		String sql = "SELECT * FROM student ORDER BY id DESC";
		return jdbcTemplate.query(sql, new StudentMapper());
	}
	
	public List<Student> findAllWithDetail() 
	{
		String sql = "SELECT s.*, l.id as lvl_history_id, l.submit_dt " +
			"FROM student s " +
			"LEFT JOIN lvlHistory l on s.id = l.student_id";
			return jdbcTemplate.query(sql, new StudentWithDetailExtractor());
	}
	
	public Student findById(Long id) 
	{
		/*String sql = "SELECT * FROM student WHERE id = :id";
		SqlParameterSource namedParameters = new MapSqlParameterSource("id", id);
		return namedParameterJdbcTemplate.queryForObject(sql, namedParameters, String.class);*/	
		Student student = jdbcTemplate.queryForObject(
				"SELECT * FROM student WHERE id = ?",
				new Object[]{id}, Student.class);
		return student;
	}
	
	private static final class StudentMapper implements RowMapper<Student> 
	{
		public Student mapRow(ResultSet rs, int rowNum) throws SQLException 
		{
			Student student = new Student();
			student.setId(rs.getLong("id"));
			return student;
		}
	}	
	
	private static final class StudentWithDetailExtractor implements
		ResultSetExtractor<List<Student>> 
	{
		public List<Student> extractData(ResultSet rs) throws SQLException, DataAccessException 
		{
			Map<Long, Student> map = new HashMap<Long, Student>();
			Student student = null;
			while (rs.next()) 
			{
				Long id = rs.getLong("id");
				student = map.get(id);
				
				if (student == null) 
				{ // new contact record
					student = new Student();
					student.setId(id);
					student.setLvlHistories(new ArrayList<LvlHistory>());
					map.put(id, student);
				}
				
				// Process LvlHistory (if exists)
				Long lvlHistoryId = rs.getLong("lvl_history_id");
				if (lvlHistoryId > 0) 
				{
					LvlHistory lvlHistory = new LvlHistory();
					lvlHistory.setId(lvlHistoryId);
					lvlHistory.setSubmitDate(rs.getDate("submit_dt"));
					//lvlHistory.setStudentId(id);
					student.getLvlHistories().add(lvlHistory);
				}
			}
			return new ArrayList<Student> (map.values());
		}
	}
	
}
